package com.bank.maven.dao.impl;


import com.bank.maven.dao.BaseDao;
import com.bank.maven.dao.Userselect;
import com.bank.maven.entity.Jilu;
import com.bank.maven.entity.User;
import com.bank.maven.entity.dao.JiluDao;
import com.bank.maven.util.ReflectUtil;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

public class UserselectImpl extends BaseDao implements Userselect {
    @Override
    public List<User> getall() throws SQLException, ClassNotFoundException, ParseException, IllegalAccessException {
        getConnection();
        String sql = "SELECT * FROM user";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<User> users = new ArrayList<>();
        while (rs.next()) {
            User user = new User();
            ReflectUtil.resultSetToObject(rs, user);
            users.add(user);
        }
        return users;
    }

    @Override
    public int adduser(Jilu jilu) throws SQLException, ClassNotFoundException {
        getConnection();
        String sql = "INSERT INTO `xinyi`.`jilu` ( `funame`, `shouname`, `shoukahao`, `time`, `money`) VALUES ( ?, ?, ?, CURRENT_TIMESTAMP, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, jilu.getFuname());
        ps.setString(2, jilu.getShouname());
        ps.setString(3, jilu.getShoukahao());
        ps.setString(4, jilu.getMoney());
        return ps.executeUpdate();
    }

    @Override
    public User getuser(int id) throws SQLException, ClassNotFoundException {
        getConnection();
        String sql = "SELECT * FROM user WHERE id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);
        ResultSet rs = ps.executeQuery();
        User user = null;
        if (rs.next()) {
            user = new User();
            user.setId(rs.getLong("id"));
            user.setName(rs.getString("name"));
            user.setKahao(rs.getString("kahao"));
            user.setIphone(rs.getString("iphone"));
            user.setMoney(rs.getLong("money"));
        }
        return user;
    }

    @Override
    public List<JiluDao> getusers(String fu, String shou, String time, int c, int limit) throws SQLException, ClassNotFoundException, IllegalAccessException {
        getConnection();
        String sql = null;
        if (fu == null || fu.equals("")) {
            fu = "%";
        }
        if (shou == null || shou.equals("")) {
            shou = "%";
        }
        if (time == null || time.equals("")) {
            time = "%";
        }
        sql = "SELECT j.id,j.funame, u.`name` shouname,j.shoukahao,left(j.time,19) time,j.money FROM `jilu` AS j,`user` AS u where u.id=j.shouname and j.funame like ? and left(j.time,10)  like ? and  u.`name` like ? order by left(j.time,19) DESC  LIMIT ?," + limit;
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, fu);
        ps.setString(2, time);
        ps.setString(3, shou);
        ps.setInt(4, (c - 1) * limit);
        ResultSet rs = ps.executeQuery();
        List<JiluDao> list = new ArrayList<>();
        while (rs.next()) {
            JiluDao jiluDao = new JiluDao();
            ReflectUtil.resultSetToObject(rs, jiluDao);
            list.add(jiluDao);
        }
        return list;
    }

    @Override
    public int[] updateuser(int money, int id, int money1, int id1) throws SQLException, ClassNotFoundException {
        getConnection();
        String sql = "UPDATE `xinyi`.`user` SET `money` = money - ? WHERE `id` = ?";
        String sql1 = "UPDATE `xinyi`.`user` SET `money` = money + ? WHERE `id` = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        PreparedStatement ps1 = conn.prepareStatement(sql1);
        ps.setInt(1, money);
        ps.setInt(2, id);
        int rowsAffected1 = ps.executeUpdate();
        ps1.setInt(1, money1);
        ps1.setInt(2, id1);
        int rowsAffected2 = ps1.executeUpdate();
        return new int[]{rowsAffected1, rowsAffected2};
    }

    @Override
    public int count(String fu, String shou, String time) throws SQLException, ClassNotFoundException {
        getConnection();
        String sql = null;
        if (fu == null || fu.equals("")) {
            fu = "%";
        }
        if (shou == null || shou.equals("")) {
            shou = "%";
        }
        if (time == null || time.equals("")) {
            time = "%";
        }
        sql = "SELECT count(1) FROM `jilu` AS j,`user` AS u where u.id=j.shouname and j.funame like ? and  u.`name` like ? and left(j.time,10) like ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, fu);
        ps.setString(2, shou);
        ps.setString(3, time);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }
}
